Hello,

I'd like to find a substitute for using SELECT DISTINCT in a derived table. Let's say I have three tables:

CREATE TABLE `trades` (
  `tradeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `employeeID` int(11) unsigned NOT NULL,
  `corporationID` int(11) unsigned NOT NULL,
  `profit` int(11) NOT NULL,
  KEY `tradeID` (`tradeID`),
  KEY `employeeID` (`employeeID`),
  KEY `corporationID` (`corporationID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `corporations` (
  `corporationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`corporationID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `employees` (
  `employeeID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`employeeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Let's say I'd like to find out how much profit a specific employee has generated. Simple:

SELECT SUM(profit) FROM trades JOIN employees ON trades.employeeID = employees.employeeID AND employees.employeeID = 1;

It gets trickier if I'd like to query how much revenue a specific corporation has, however. I cannot simply replicate the aforementioned query, because two or more employees from the same company might be involved in the same trade. This query should do the trick:

SELECT SUM(profit) FROM trades JOIN (SELECT DISTINCT tradeID FROM trades WHERE trades.corporationID = 1)

... unfortunately, DISTINCT JOINs seem crazy ineffective. Is there any alternative I can use to determine how much revenue a corporation has, taking into account that a corporation might be listed several times with the same tradeID?